﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Collections;
namespace Exact_Extension
{
    public class Common
    {

        ExactDataContext DS;
        IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);
        public Common(ExactDataContext _DS)
        {
            DS = _DS;



        }
        public bool CheckStudentExist(string StudentID)
        {
            var queryCC = from CC in DS.cicmpies
                          where CC.textfield4 == StudentID
                          select CC;
            if (queryCC.Count() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool CheckDiscountExist(string StudentID)
        {
            var query = from Dis in DS.ISHCMC_Discounts
                        where Dis.StudentID == StudentID
                        select Dis;
            if (query.Count() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool CheckDiscountExist(string StudentID,string Discountype)
        {
            var query = from Dis in DS.ISHCMC_Discounts
                        where Dis.StudentID == StudentID && Dis.DiscountType == Discountype
                        select Dis;
            if (query.Count() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool CheckInvoiceExist(ISHCMC_V_StudentListWithDebtor itemStudent, string itemcode, string schoolyear,DateTime InvoiceDate,ISHCMC_Section itemSec )
        {

            string sectioncode = itemSec.SectionCode;
            if (itemcode != "TF")
            {
                sectioncode = "A";
            }
            var query = from Inv in DS.ISHCMC_Invoices
                        where Inv.StudentID == itemStudent.StudentID && Inv.Status == 0 && Inv.Itemcode == itemcode
                        && Inv.SchoolYear == schoolyear && Inv.Section == sectioncode
                        select Inv;
            if (query.Count() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool CheckInvoiceHeaderExist(string StudentID, string schoolyear,string school)
        {
            var query = from Inv in DS.ISHCMC_InvoiceHeaders
                        where Inv.StudentID == StudentID && Inv.Status == 0
                        && Inv.SchoolYear == schoolyear && Inv.School == school
                        select Inv;
            if (query.Count() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public int GetInvoiceHeaderDisplayMaxID(string school)
        {
            var query = from Inv in DS.ISHCMC_InvoiceHeaders
                        where Inv.School == school
                        select Inv.InvoiceDisplayID;

            if (query.Max().HasValue)
            {
                return query.Max().Value;
            }
            else
            {
                return 0;
            }
        }
        public ISHCMC_InvoiceHeader GetInvoiceHeader(string studentID, string schoolyear,string school)
        {
            var query = from Inv in DS.ISHCMC_InvoiceHeaders
                        where Inv.StudentID == studentID && Inv.Status == 0
                        && Inv.SchoolYear == schoolyear && Inv.School == school
                        select Inv;
            if (query.Count() > 0)
            {
                return query.First();
            }
            else
            {
                return null;
            }
          
        }
        public ISHCMC_Section GetSection(string paymentmethod, string schoolyear, string school,DateTime InvoiceDate)
        {
            var query = from Sec in DS.ISHCMC_Sections
                        where Sec.PaymentMethod == paymentmethod && Sec.SchoolYear == schoolyear && Sec.School == school
                        select Sec;
            ISHCMC_Section item = new ISHCMC_Section();
           
            foreach (ISHCMC_Section itemSub in query)
            {
                if (InvoiceDate >= itemSub.StartDate && InvoiceDate <= itemSub.EndDate)
                {
                    item = itemSub;
                }
            }
            return item;

        }
        public ISHCMC_Section GetSection(string paymentmethod, string schoolyear, string school, DateTime InvoiceDate,string SectionCode)
        {
            var query = from Sec in DS.ISHCMC_Sections
                        where Sec.PaymentMethod == paymentmethod && Sec.SchoolYear == schoolyear && Sec.School == school
                        select Sec;
            ISHCMC_Section item = new ISHCMC_Section();

            foreach (ISHCMC_Section itemSub in query)
            {
                if (itemSub.SectionCode == SectionCode)
                {
                    item = itemSub;
                }
            }
            return item;

        }
        public decimal CalculateDiscount(string debcode, string studentid, string itemcode)
        {
            decimal totalDis = 0;
            var query = from Dis in DS.ISHCMC_V_DiscountLists
                        where Dis.StudentID == studentid && Dis.Debcode == debcode && Dis.ItemImpact == itemcode && Dis.status ==0
                        select Dis;
            foreach (ISHCMC_V_DiscountList item in query)
            {
                if (item.Percentage.HasValue)
                {
                    totalDis += item.Percentage.Value;
                }
            }
            return totalDis;
        }
        public string GetPriceListCode(string itemcode, ISHCMC_V_StudentListWithDebtor itemStudent, string schoolyear, DateTime InvoiceDate)
        {
            ISHCMC_Section itemSec = new ISHCMC_Section();
            if (itemStudent.PaymentMethod == null)
            {
                itemSec = this.GetSection("A", schoolyear, itemStudent.School, InvoiceDate);
            }
            else
            {
                itemSec = this.GetSection(itemStudent.PaymentMethod, schoolyear, itemStudent.School, InvoiceDate);
            }

            ISHCMC_V_Item itemFee = DS.ISHCMC_V_Items.Single(c => c.ItemCode == itemcode);

            string pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "_"
                + itemSec.SectionCode + "_" + schoolyear.Substring(2, 2);
            if (itemStudent.Grade.StartsWith("E2"))
            {
                if (itemStudent.HalfDay == 0)
                {
                    pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "F_"
                    + itemSec.SectionCode + "_" + schoolyear.Substring(2, 2);
                }
                else if (itemStudent.HalfDay == 1)
                {
                    pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "H_"
                 + itemSec.SectionCode + "_" + schoolyear.Substring(2, 2);
                }
            }
            if (itemFee.Assortment == 1 && itemFee.ItemCode == "TF")
            {
                return pricelistcode;
            }
            else
            {
                return itemStudent.School;  
            }
          
        }
        public string GetPriceListCode(string itemcode, ISHCMC_V_StudentListWithDebtor itemStudent, string schoolyear, DateTime InvoiceDate,string sectioncode)
        {
            ISHCMC_Section itemSec = new ISHCMC_Section();
            if (itemStudent.PaymentMethod == null)
            {
                itemSec = this.GetSection("A", schoolyear, itemStudent.School, InvoiceDate,sectioncode);
            }
            else
            {
                itemSec = this.GetSection(itemStudent.PaymentMethod, schoolyear, itemStudent.School, InvoiceDate,sectioncode);
            }

            ISHCMC_V_Item itemFee = DS.ISHCMC_V_Items.Single(c => c.ItemCode == itemcode);

            string pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "_"
                + itemSec.SectionCode + "_" + schoolyear.Substring(2, 2);
            if (itemStudent.Grade.StartsWith("E2"))
            {
                if (itemStudent.HalfDay == 0)
                {
                    pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "F_"
                    + itemSec.SectionCode + "_" + schoolyear.Substring(2, 2);
                }
                else if (itemStudent.HalfDay == 1)
                {
                    pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "H_"
                 + itemSec.SectionCode + "_" + schoolyear.Substring(2, 2);
                }
            }
            if (itemFee.Assortment == 1 && itemFee.ItemCode == "TF")
            {
                return pricelistcode;
            }
            else
            {
                return itemStudent.School;
            }

        }
        public void GenerateInvoice(string itemcode, ISHCMC_V_StudentListWithDebtor itemStudent, string schoolyear, DateTime InvoiceDate,ISHCMC_Section itemSec,bool fixterm ,string termcode) // type : Education or NonEducation
        {


   
                string invoiceNo = "";
                string profromInv = "";
                string strProfromInv = "";
                ISHCMC_InvoiceHeader InvHeader = new ISHCMC_InvoiceHeader();
                string paymentmethod = itemSec.Descriptions;
                if (itemSec.DueDate.HasValue)
                {
                    DateTime duedate = itemSec.DueDate.Value;
                }
                DateTime duedatePayment;
                decimal amountPercentLateEnroll = 0;
                decimal amountPercentFixTerm = 0;
                decimal amount = 0;
                string descriptioninvoice = "";
                if (InvoiceDate.Date < Convert.ToDateTime("01/06/" + itemStudent.SchoolYear.Split(new char[1] { '-' })[0], culture))
                {
                    duedatePayment = Convert.ToDateTime("01/07/" + itemStudent.SchoolYear.Split(new char[1] { '-' })[0], culture);
                }
                
                if (CheckInvoiceHeaderExist(itemStudent.StudentID, schoolyear,itemStudent.School))
                {
                    InvHeader = this.GetInvoiceHeader(itemStudent.StudentID, schoolyear,itemStudent.School);

                    duedatePayment = InvoiceDate.Date.AddDays(7);
                }

                else
                {

                    duedatePayment = InvoiceDate.Date.AddDays(30);
                    ISHCMC_InvoiceHeader InvHeaderAdd = new ISHCMC_InvoiceHeader()
                    {
                        Debcode = itemStudent.DebtorCode,
                        Debnr = itemStudent.DebtorCode.Trim(),
                        StudentID = itemStudent.StudentID,
                        Status = 0,

                        DueDate = duedatePayment,
                        SchoolYear = schoolyear,
                        School = itemStudent.School,
                        InvoiceDisplayID = this.GetInvoiceHeaderDisplayMaxID(itemStudent.School) + 1,
                        Syscreated = DateTime.Now,
                        Sysmodified = DateTime.Now,
                        Syscreator = Environment.UserName,
                        Sysmodifier = Environment.UserName

                    };
                    DS.ISHCMC_InvoiceHeaders.InsertOnSubmit(InvHeaderAdd);
                    DS.SubmitChanges();
                    InvHeader = this.GetInvoiceHeader(itemStudent.StudentID, schoolyear,itemStudent.School);

                }
                int invoiceDisplayID = InvHeader.InvoiceDisplayID.Value;
                if (!CheckInvoiceExist(itemStudent, itemcode, schoolyear, InvoiceDate, itemSec))
                {




                    

                    ISHCMC_V_Item itemFee = DS.ISHCMC_V_Items.Single(c => c.ItemCode == itemcode);
                    if (fixterm)
                    {
                        itemSec.SectionCode = "A";
                    }
                    string pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "_"
                        + itemSec.SectionCode + "_" + schoolyear.Substring(2, 2);
                    if (itemStudent.Grade.StartsWith("E2"))
                    {
                        if (itemStudent.HalfDay == 0)
                        {
                            pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "F_"
                            + itemSec.SectionCode + "_" + schoolyear.Substring(2, 2);
                        }
                        else if (itemStudent.HalfDay == 1)
                        {
                            pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "H_"
                         + itemSec.SectionCode + "_" + schoolyear.Substring(2, 2);
                        }
                    }
                    staffl itemPL = new staffl();
                    stfom itemFM = new stfom();
                    
                        if (itemFee.Assortment == 1 && itemFee.ItemCode == "TF")
                        {
                            itemFM = DS.stfoms.First(c => c.prijslijst == pricelistcode);
                            itemPL = DS.staffls.First(c => c.prijslijst == pricelistcode);
                        }
                        else
                        {
                            itemPL = DS.staffls.First(c => c.prijslijst == itemStudent.School && c.artcode == itemcode);
                            itemFM = DS.stfoms.First(c => c.prijslijst == itemPL.prijslijst);
                        }




                    invoiceNo = invoiceDisplayID.ToString();
                    profromInv = invoiceDisplayID.ToString();
                    while (invoiceNo.Length < 5)
                    {
                        invoiceNo = "0" + invoiceNo;
                        profromInv = "0" + profromInv;
                    }
                    
             

                    strProfromInv = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + profromInv + "-" + itemSec.SectionCode + "-" + itemStudent.School;
                    amount = Convert.ToDecimal(itemPL.prijs83);
                    if (fixterm)
                    {
                        if (itemFee.ItemCode == "APPF" && itemFee.Assortment == 2)
                        {
                            invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + "A" + "-APP-" + itemStudent.School.Substring(0, 2);
                            descriptioninvoice = "Application fee " + itemStudent.SchoolYear;
                        }
                        if (itemFee.ItemCode == "COE" && itemFee.Assortment == 2)
                        {
                            invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + "A" + "-COE-" + itemStudent.School.Substring(0, 2);
                            descriptioninvoice = "COE Purchase " + itemStudent.SchoolYear + " / Mua COE " + itemStudent.SchoolYear;
                        }
                        if (itemFee.ItemCode == "REENF" && itemFee.Assortment == 2)
                        {
                            invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + "A" + "-EN-" + itemStudent.School.Substring(0, 2);
                            descriptioninvoice = "REENF " + itemStudent.SchoolYear;
                        }
                        if (itemFee.Assortment == 1)
                        {

                            invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + "A" + "-TF-" + itemStudent.School.Substring(0, 2);
                            if (itemFee.ItemCode == "ADF")
                            {
                                descriptioninvoice = "Annual Development Fee " + itemStudent.SchoolYear;
                            }
                            if (itemFee.ItemCode == "TF")
                            {
                                descriptioninvoice = "School Fee " + itemStudent.SchoolYear;
                            }
                        }
                        amountPercentFixTerm = this.GetFixTerm(itemcode, termcode);
                        strProfromInv = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + profromInv + "-" + "A" + "-" + itemStudent.School;
                    }
                    else
                    {
                        if (itemFee.ItemCode == "APPF" && itemFee.Assortment == 2)
                        {
                            invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + itemSec.SectionCode + "-APP-" + itemStudent.School.Substring(0, 2);
                            descriptioninvoice = "Application fee " + itemStudent.SchoolYear;
                        }
                        if (itemFee.ItemCode == "COE" && itemFee.Assortment == 2)
                        {
                            invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + itemSec.SectionCode + "-COE-" + itemStudent.School.Substring(0, 2);
                            descriptioninvoice = "COE Purchase " + itemStudent.SchoolYear + " / Mua COE " + itemStudent.SchoolYear;
                        }
                        if (itemFee.ItemCode == "REENF" && itemFee.Assortment == 2)
                        {
                            invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + itemSec.SectionCode + "-EN-" + itemStudent.School.Substring(0, 2);
                            descriptioninvoice = "REENF " + itemStudent.SchoolYear;
                        }
                        if (itemFee.Assortment == 1)
                        {

                            invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + itemSec.SectionCode + "-TF-" + itemStudent.School.Substring(0, 2);
                            if (itemFee.ItemCode == "ADF")
                            {
                                descriptioninvoice = "Annual Development Fee " + itemStudent.SchoolYear;
                            }
                            if (itemFee.ItemCode == "TF")
                            {
                                descriptioninvoice = "School Fee " + itemStudent.SchoolYear;
                            }
                        }
                    }
                   
                        amountPercentLateEnroll = this.GetLateEnrollment(itemcode, itemStudent);

                        if (fixterm)
                        {
                            if (amountPercentFixTerm > 0)
                            {
                                amount = amount * amountPercentFixTerm;
                            }
                        }
                        else
                        {
                            if (amountPercentLateEnroll > 0)
                            {
                                amount = amount * amountPercentLateEnroll;
                            }
                        }
                    ISHCMC_Invoice itemInv = new ISHCMC_Invoice
                    {
                        Debcode = itemStudent.DebtorCode,
                        Debnr = itemStudent.DebtorCode.Trim(),
                        StudentID = itemStudent.StudentID,

                        Itemcode = itemFee.ItemCode,
                        Amount = amount,
                        Discount = this.CalculateDiscount(itemStudent.DebtorCode, itemStudent.StudentID, itemFee.ItemCode),
                        
                        TransDate = InvoiceDate.Date,
                        Status = 0,
                        Section = itemSec.SectionCode,
                        Description = descriptioninvoice,
                        DueDate = duedatePayment,
                        SchoolYear = schoolyear,
                        SpecialDiscount = false,
                        Authorize = false,
                        InvoiceHeaderID = InvHeader.ID,
                        InvoiceNo = invoiceNo,
                        ProformaInvoiceNo = strProfromInv,
                        Currency = itemFM.valcode,
                        Syscreated = DateTime.Now,
                        Sysmodified = DateTime.Now,
                        Syscreator = Environment.UserName,
                        Sysmodifier = Environment.UserName



                    };
                    if (itemFee.ItemCode != "TF")
                    {
                        itemInv.Section = "A";
                    }
                    DS.ISHCMC_Invoices.InsertOnSubmit(itemInv);
                    DS.SubmitChanges();
                  
                    this.ProcessTrackingInvoice(itemInv, "Create new invoice detail", "Create new");
                }
            
          



        }
        public void GenerateInvoice(string itemcode, ISHCMC_V_StudentListWithDebtor itemStudent, string schoolyear, DateTime InvoiceDate, ISHCMC_Section itemSec) // type : Education or NonEducation
        {



            string invoiceNo = "";
            string profromInv = "";
            string strProfromInv = "";
            ISHCMC_InvoiceHeader InvHeader = new ISHCMC_InvoiceHeader();
            string paymentmethod = itemSec.Descriptions;
            if (itemSec.DueDate.HasValue)
            {
                DateTime duedate = itemSec.DueDate.Value;
            }
            DateTime duedatePayment;
        
            decimal amount = 0;
            string descriptioninvoice = "";
            if (InvoiceDate.Date < Convert.ToDateTime("01/06/" + itemStudent.SchoolYear.Split(new char[1] { '-' })[0], culture))
            {
                duedatePayment = Convert.ToDateTime("01/07/" + itemStudent.SchoolYear.Split(new char[1] { '-' })[0], culture);
            }

            if (CheckInvoiceHeaderExist(itemStudent.StudentID, schoolyear, itemStudent.School))
            {
                InvHeader = this.GetInvoiceHeader(itemStudent.StudentID, schoolyear, itemStudent.School);

                duedatePayment = InvoiceDate.Date.AddDays(7);
            }

            else
            {

                duedatePayment = InvoiceDate.Date.AddDays(30);
                ISHCMC_InvoiceHeader InvHeaderAdd = new ISHCMC_InvoiceHeader()
                {
                    Debcode = itemStudent.DebtorCode,
                    Debnr = itemStudent.DebtorCode.Trim(),
                    StudentID = itemStudent.StudentID,
                    Status = 0,

                    DueDate = duedatePayment,
                    SchoolYear = schoolyear,
                    School = itemStudent.School,
                    InvoiceDisplayID = this.GetInvoiceHeaderDisplayMaxID(itemStudent.School) + 1,
                    Syscreated = DateTime.Now,
                    Sysmodified = DateTime.Now,
                    Syscreator = Environment.UserName,
                    Sysmodifier = Environment.UserName

                };
                DS.ISHCMC_InvoiceHeaders.InsertOnSubmit(InvHeaderAdd);
                DS.SubmitChanges();
                InvHeader = this.GetInvoiceHeader(itemStudent.StudentID, schoolyear, itemStudent.School);

            }
            int invoiceDisplayID = InvHeader.InvoiceDisplayID.Value;
            if (!CheckInvoiceExist(itemStudent, itemcode, schoolyear, InvoiceDate, itemSec))
            {






                ISHCMC_V_Item itemFee = DS.ISHCMC_V_Items.Single(c => c.ItemCode == itemcode);
              
                string pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "_"
                    + itemSec.SectionCode + "_" + schoolyear.Substring(2, 2);
                if (itemStudent.Grade.StartsWith("E2"))
                {
                    if (itemStudent.HalfDay == 0)
                    {
                        pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "F_"
                        + itemSec.SectionCode + "_" + schoolyear.Substring(2, 2);
                    }
                    else if (itemStudent.HalfDay == 1)
                    {
                        pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "H_"
                     + itemSec.SectionCode + "_" + schoolyear.Substring(2, 2);
                    }
                }
                staffl itemPL = new staffl();
                stfom itemFM = new stfom();
               
                    if (itemFee.Assortment == 1 && itemFee.ItemCode == "TF")
                    {
                        itemFM = DS.stfoms.First(c => c.prijslijst == pricelistcode);
                        itemPL = DS.staffls.First(c => c.prijslijst == pricelistcode);
                    }
                    else
                    {
                        itemPL = DS.staffls.First(c => c.prijslijst == itemStudent.School && c.artcode == itemcode);
                        itemFM = DS.stfoms.First(c => c.prijslijst == itemPL.prijslijst);
                    }
              



                invoiceNo = invoiceDisplayID.ToString();
                profromInv = invoiceDisplayID.ToString();
                while (invoiceNo.Length < 5)
                {
                    invoiceNo = "0" + invoiceNo;
                    profromInv = "0" + profromInv;
                }



                strProfromInv = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + profromInv + "-" + itemSec.SectionCode + "-" + itemStudent.School;
                amount = Convert.ToDecimal(itemPL.prijs83);
                 if (itemFee.ItemCode == "APPF" && itemFee.Assortment == 2)
                    {
                        invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + itemSec.SectionCode + "-APP-" + itemStudent.School.Substring(0, 2);
                        descriptioninvoice = "Application fee " + itemStudent.SchoolYear;
                    }
                    if (itemFee.ItemCode == "COE" && itemFee.Assortment == 2)
                    {
                        invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + itemSec.SectionCode + "-COE-" + itemStudent.School.Substring(0, 2);
                        descriptioninvoice = "COE Purchase " + itemStudent.SchoolYear + " / Mua COE " + itemStudent.SchoolYear;
                    }
                    if (itemFee.ItemCode == "REENF" && itemFee.Assortment == 2)
                    {
                        invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + itemSec.SectionCode + "-EN-" + itemStudent.School.Substring(0, 2);
                        descriptioninvoice = "REENF " + itemStudent.SchoolYear;
                    }
                    if (itemFee.Assortment == 1)
                    {

                        invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + itemSec.SectionCode + "-TF-" + itemStudent.School.Substring(0, 2);
                        if (itemFee.ItemCode == "ADF")
                        {
                            descriptioninvoice = "Annual Development Fee " + itemStudent.SchoolYear;
                        }
                        if (itemFee.ItemCode == "TF")
                        {
                            descriptioninvoice = "School Fee " + itemStudent.SchoolYear;
                        }
                    }
                

              
                ISHCMC_Invoice itemInv = new ISHCMC_Invoice
                {
                    Debcode = itemStudent.DebtorCode,
                    Debnr = itemStudent.DebtorCode.Trim(),
                    StudentID = itemStudent.StudentID,

                    Itemcode = itemFee.ItemCode,
                    Amount = amount,
                    Discount = this.CalculateDiscount(itemStudent.DebtorCode, itemStudent.StudentID, itemFee.ItemCode),

                    TransDate = InvoiceDate.Date,
                    Status = 0,
                    Section = itemSec.SectionCode,
                    Description = descriptioninvoice,
                    DueDate = duedatePayment,
                    SchoolYear = schoolyear,
                    SpecialDiscount = false,
                    Authorize = false,
                    InvoiceHeaderID = InvHeader.ID,
                    InvoiceNo = invoiceNo,
                    ProformaInvoiceNo = strProfromInv,
                    Currency = itemFM.valcode,
                    Syscreated = DateTime.Now,
                    Sysmodified = DateTime.Now,
                    Syscreator = Environment.UserName,
                    Sysmodifier = Environment.UserName



                };
                if (itemFee.ItemCode != "TF")
                {
                    itemInv.Section = "A";
                }
                DS.ISHCMC_Invoices.InsertOnSubmit(itemInv);
                DS.SubmitChanges();

                this.ProcessTrackingInvoice(itemInv, "Create new invoice detail", "Create new");
            }





        }
        public void GenerateOTHInvoice(string itemcode, ISHCMC_V_StudentListWithDebtor itemStudent, string schoolyear, DateTime InvoiceDate, ISHCMC_Section itemSec, decimal amount,string itemdesc) // type : Education or NonEducation
        {


          
                string invoiceNo = "";
                string profromInv = "";

                ISHCMC_InvoiceHeader InvHeader = new ISHCMC_InvoiceHeader();
                string paymentmethod = itemSec.Descriptions;
               // DateTime duedate = itemSec.DueDate.Value;
                DateTime duedatePayment;
               
           

                if (CheckInvoiceHeaderExist(itemStudent.StudentID, schoolyear, itemStudent.School))
                {
                    InvHeader = this.GetInvoiceHeader(itemStudent.StudentID, schoolyear, itemStudent.School);

                    duedatePayment = InvoiceDate.Date.AddDays(7);
                }

                else
                {

                    duedatePayment = InvoiceDate.Date.AddDays(7);
                    ISHCMC_InvoiceHeader InvHeaderAdd = new ISHCMC_InvoiceHeader()
                    {
                        Debcode = itemStudent.DebtorCode,
                        Debnr = itemStudent.DebtorCode.Trim(),
                        StudentID = itemStudent.StudentID,
                        Status = 0,

                        DueDate = duedatePayment,
                        SchoolYear = schoolyear,
                        School = itemStudent.School,
                        InvoiceDisplayID = this.GetInvoiceHeaderDisplayMaxID(itemStudent.School) + 1,
                        Syscreated = DateTime.Now,
                        Sysmodified = DateTime.Now,
                        Syscreator = Environment.UserName,
                        Sysmodifier = Environment.UserName

                    };
                    DS.ISHCMC_InvoiceHeaders.InsertOnSubmit(InvHeaderAdd);
                    DS.SubmitChanges();
                    InvHeader = this.GetInvoiceHeader(itemStudent.StudentID, schoolyear, itemStudent.School);

                }
                int invoiceDisplayID = InvHeader.InvoiceDisplayID.Value;
                if (!CheckInvoiceExist(itemStudent, itemcode, schoolyear, InvoiceDate, itemSec))
                {






                    ISHCMC_V_Item itemFee = DS.ISHCMC_V_Items.Single(c => c.ItemCode == itemcode);

                   
                    


                    invoiceNo = invoiceDisplayID.ToString();
                    profromInv = invoiceDisplayID.ToString();
                    while (invoiceNo.Length < 5)
                    {
                        invoiceNo = "0" + invoiceNo;
                        profromInv = "0" + profromInv;
                    }


                    invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + itemcode;

                    profromInv = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + profromInv + "-" + itemcode;
                   
                    ISHCMC_Invoice itemInv = new ISHCMC_Invoice
                    {
                        Debcode = itemStudent.DebtorCode,
                        Debnr = itemStudent.DebtorCode.Trim(),
                        StudentID = itemStudent.StudentID,

                        Itemcode = itemFee.ItemCode,
                        Amount = amount,
                        Discount = this.CalculateDiscount(itemStudent.DebtorCode, itemStudent.StudentID, itemFee.ItemCode),

                        TransDate = InvoiceDate.Date,
                        Status = 0,
                        Section = itemSec.SectionCode,
                        Description = itemdesc,
                        DueDate = duedatePayment,
                        SchoolYear = schoolyear,
                        SpecialDiscount = false,
                        Authorize = false,
                        InvoiceHeaderID = InvHeader.ID,
                        InvoiceNo = invoiceNo,
                        ProformaInvoiceNo = profromInv,
                        Currency = "VND",
                        Syscreated = DateTime.Now,
                        Sysmodified = DateTime.Now,
                        Syscreator = Environment.UserName,
                        Sysmodifier = Environment.UserName



                    };
                    
                    DS.ISHCMC_Invoices.InsertOnSubmit(itemInv);
                    DS.SubmitChanges();
                    //XMLExport xmlExport = new XMLExport(DS);
                    //string filename = xmlExport.ExportSalesInvoice(itemStudent, itemInv, schoolyear);
                    //xmlExport.StartProcessImportInvoice(filename);
                    //this.UpdateCostUnitForInvoiceLine(itemInv, itemStudent);
                    this.ProcessTrackingInvoice(itemInv, "Create new invoice detail", "Create new");
               
            }




        }
        public decimal GetLateEnrollment(string itemcode, ISHCMC_V_StudentListWithDebtor itemStudent)
        {
            decimal amount = 0;
            var queryFT = from TFT in DS.ISHCMC_V_LateEnrolls
                          where TFT.ItemCode == itemcode && itemStudent.EnrollmentDate >= TFT.EntryStartDate
                          && itemStudent.EnrollmentDate <= TFT.EntryEndDate
                          select TFT;
            if (queryFT.Count() > 0)
            {
                if (itemStudent.PaymentMethod == "A")
                {
                    ISHCMC_V_LateEnroll itemLateEnroll = queryFT.First();
                    if (itemLateEnroll.Percentage.HasValue)
                    {
                        amount = itemLateEnroll.Percentage.Value / 100;
                    }
                }

            }
            return amount;
            
        }
        public decimal GetFixTerm(string itemcode, string TermCode)
        {
            decimal amount = 0;
            var queryFT = from TFT in DS.ISHCMC_FixedTermFees
                          where TFT.ItemCode == itemcode && TFT.TermCode == TermCode
                          select TFT;
            if (queryFT.Count() > 0)
            {
                ISHCMC_FixedTermFee itemFTF = queryFT.First();
                if (itemFTF.Percentage.HasValue)
                {
                    amount = itemFTF.Percentage.Value / 100;
                }

            }
            return amount;

        }
        public void UpdateCostUnitForInvoiceLine(ISHCMC_V_Invoice Inv,ISHCMC_V_StudentListWithDebtor Student)
        {
            var query = from InvExact in DS.frsrgs
                        where InvExact.docnumber == Inv.ProformaInvoiceNo + "/" + Inv.Itemcode
                        select InvExact;
            foreach(frsrg InvCheck in query)
            {
                string Grade = "";
                if (Student.Grade.StartsWith("E"))
                {
                    Grade = Student.Grade.Replace("E", "EE ");
                }
                else
                {
                    Grade = "GR " + Student.Grade;
                }
                frsrg InvUpdate = DS.frsrgs.Single(c => c.ID == InvCheck.ID);
                InvUpdate.kstdrcode = Grade;
            }
           
                DS.SubmitChanges();


        }
        public void PickCOE(ISHCMC_V_StudentListWithDebtor itemStudent, DateTime PurchaseDate)
        {
            var queryCOE = from COE in DS.ISHCMC_COEs
                           where COE.COEStatus == 1
                           orderby COE.COESeq ascending
                           select COE;
            if (queryCOE.Count() > 0)
            {
                ISHCMC_COE itemCOE = queryCOE.First();
                ISHCMC_COETran itemCOETran = new ISHCMC_COETran
                {
                    COENumber = itemCOE.COENumber,
                    Invoicing = true,
                    PurchaseDate = PurchaseDate,
                    School = itemStudent.School,
                    StudentID = itemStudent.StudentID
                };
                DS.ISHCMC_COETrans.InsertOnSubmit(itemCOETran);
                DS.SubmitChanges();

                this.UpdateCOEStatus(itemCOE.COENumber);
                this.UpdateCOEForStudent(itemStudent, itemCOE.COENumber);

            }
        }
        private void UpdateCOEStatus(int COENumber)
        {
            ISHCMC_COE itemCOE = DS.ISHCMC_COEs.Single(c => c.COENumber == COENumber);
            itemCOE.COEStatus = 1;
            DS.SubmitChanges();
        }
        public void UpdateCOEForStudent(ISHCMC_V_StudentListWithDebtor itemStudent, int COENumber)
        {
            cicmpy studentUpdate = DS.cicmpies.Single(c => c.textfield4 == itemStudent.StudentID);

            studentUpdate.YesNofield1 = 1;
            studentUpdate.numberfield1 = Convert.ToDouble(COENumber.ToString());

            DS.SubmitChanges();
        }
        public void ProcessTrackingInvoice(ISHCMC_Invoice itemInv, string comment,string action)
        {
            ISHCMC_InvoiceTracking itemTrack = new ISHCMC_InvoiceTracking
            {
                InvoiceHeaderID = itemInv.InvoiceHeaderID,
                StudentID = itemInv.StudentID,
                InvoiceDetailID = itemInv.ID,
                Comment = comment,
                TrackDate = DateTime.Now,
                InvolvePerson = Environment.UserName,
                InvoiceAmount = itemInv.Amount,
                InvoiceDiscount = itemInv.Discount,
                Section = itemInv.Section,
                Action = action,
                ItemCode = itemInv.Itemcode,
                Syscreated = DateTime.Now,
                Sysmodified = DateTime.Now,
                Syscreator = Environment.UserName,
                Sysmodifier = Environment.UserName


            };
            DS.ISHCMC_InvoiceTrackings.InsertOnSubmit(itemTrack);
            DS.SubmitChanges();
        }

        public int DeleteInvoice(ISHCMC_Invoice itemInv)
        {
            var query = from Inv in DS.ISHCMC_Invoices
                        where Inv.ID == itemInv.ID
                        select Inv;
            if (query.Count() > 0)
            {
                ISHCMC_Invoice InvUpdate = DS.ISHCMC_Invoices.Single(c => c.ID == itemInv.ID);
                InvUpdate.Status = 1;
                DS.SubmitChanges();

               // this.DeleteRecordInvoiceInExact(InvUpdate);

                return 1;
            }
            else
            {
                return 0;
            }
        }
        private void DeleteRecordInvoiceInExact(ISHCMC_Invoice itemInv)
        {
            // Query the database for the rows to be deleted.
            var deleteInv =
                from Inv in DS.frsrgs
                where Inv.docnumber == itemInv.ProformaInvoiceNo + "/" + itemInv.Itemcode
                select Inv;
            frsrg  InvRoot = deleteInv.First();
            foreach (var detail in deleteInv)
            {
                DS.frsrgs.DeleteOnSubmit(detail);
            }
            var deleteInvH = from InvH in DS.frkrgs
                             where InvH.volgnr5 == InvRoot.volgnr5 && InvH.fakt_code == InvRoot.fakt_code
                             select InvH;
            foreach (var header in deleteInvH)
            {
                DS.frkrgs.DeleteOnSubmit(header);
            }

            var deleteBT = from BT in DS.BankTransactions
                           where BT.SequenceNumber == InvRoot.volgnr5 && BT.InvoiceCode == InvRoot.fakt_code && BT.Type == 'W'
                           select BT;
            foreach (var BTItem in deleteBT)
            {
                DS.BankTransactions.DeleteOnSubmit(BTItem);
            }
            try
            {
                DS.SubmitChanges();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                // Provide for exceptions.
            }
        }
        public int COETransfer(ISHCMC_StudentCOEAssigned STAssigned, ISHCMC_StudentCOEAvailable STAvailable,string remark)
        {
            try
            {
                

                cicmpy STAvailableUpdate = DS.cicmpies.Single(c => c.cmp_code == STAvailable.DebtorCode);

                STAvailableUpdate.YesNofield1 = 1;
                STAvailableUpdate.numberfield1 = STAssigned.COENumber;



                cicmpy STAssignedUpdate = DS.cicmpies.Single(c => c.cmp_code == STAssigned.DebtorCode);
                STAssignedUpdate.YesNofield1 = 0;
                STAssignedUpdate.numberfield1 = 0;


                ISHCMC_COETransfer itemTransfer = new ISHCMC_COETransfer()
                {
                    FromStudentID = STAssigned.StudentID,
                    ToStudentID = STAvailable.StudentID,
                    COENumber = STAssigned.COENumber,
                    TransferDate = DateTime.Now.Date,
                    Syscreated = DateTime.Now,
                    Sysmodified = DateTime.Now,
                    Syscreator = Environment.UserName,
                    Sysmodifier = Environment.UserName,
                    Remarks = remark
                };
                DS.ISHCMC_COETransfers.InsertOnSubmit(itemTransfer);


                var queryInvDetail = from Inv in DS.ISHCMC_Invoices
                                     where Inv.StudentID == STAvailable.StudentID && Inv.Itemcode == "ADF"
                                     && Inv.SchoolYear == STAvailable.SchoolYear.Substring(5, 4)
                                     select Inv;
                if (queryInvDetail.Count() > 0)
                {
                    foreach (ISHCMC_Invoice InvD in queryInvDetail)
                    {
                        ISHCMC_Invoice InvDUpdate = DS.ISHCMC_Invoices.Single(c => c.ID == InvD.ID);
                        InvDUpdate.Status = 1;

                    }
                }

                DS.SubmitChanges();



                return 1;
            }
            catch (Exception ex)
            {
                return 0;
            }
            
        }
        public int COEPurchase(ISHCMC_V_StudentListWithDebtor itemStudent)
        {
            try
            {
                ISHCMC_COETran itemPurchase = new ISHCMC_COETran
                {
                    StudentID = itemStudent.StudentID,
                    School = itemStudent.School
                };
                DS.ISHCMC_COETrans.InsertOnSubmit(itemPurchase);

                cicmpy studentUpdate = DS.cicmpies.Single(c => c.textfield4 == itemStudent.StudentID);
                studentUpdate.YesNofield1 = 1;

                DS.SubmitChanges();
                return 1;
            }
            catch (Exception ex)
            {
                return 0;
            }
            
        }
        public bool CheckStudentAssignedCOE(string StudentID)
        {
            var query = from ST in DS.ISHCMC_V_StudentListWithDebtors
                        where ST.StudentID == StudentID && ST.COENumber >0
                        select ST;
            if (query.Count() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
            
        }
        public ISHCMC_StudentPayment GetStudentPayment(string StudentID)
        {

            var query = from Paid in DS.ISHCMC_StudentPayments
                        where Paid.StudentID == StudentID
                        select Paid;
            if (query.Count() > 0)
            {
                return query.First();
            }
            else
            {
                var queryInv = from InvoicePaid in DS.ISHCMC_InvoicePayments
                               where InvoicePaid.StudentID == StudentID
                               select InvoicePaid;
                if (queryInv.Count() > 0)
                {

                    ISHCMC_StudentPayment Payment = new ISHCMC_StudentPayment();
                    Payment.TuitionAmountPaid = 0;
                    Payment.ReenrollAmountPaid = 0;
                    Payment.ApplicationFeePaid = 0;
                    foreach (ISHCMC_InvoicePayment InvP in queryInv)
                    {
                        if (InvP.ExcludeInvoice != true)
                        {
                            Payment.StudentID = InvP.StudentID;
                            Payment.PaymentDate = InvP.PaymentDate;
                            Payment.PaymentType = InvP.PaymentType;
                            Payment.Reference = InvP.ReferenceNo;
                          
                            if (InvP.ItemCode == "TF")
                            {
                                if (InvP.PaidAmount.HasValue)
                                    Payment.TuitionAmountPaid = Payment.TuitionAmountPaid.Value + InvP.PaidAmount.Value;
                            }
                            if (InvP.ItemCode == "ADF")
                            {
                                if (InvP.PaidAmount.HasValue)
                                    Payment.TuitionAmountPaid = Payment.TuitionAmountPaid.Value + InvP.PaidAmount.Value;
                            }
                            if (InvP.ItemCode == "APPF")
                            {
                                if (InvP.PaidAmount.HasValue)
                                    Payment.ApplicationFeePaid = Payment.ApplicationFeePaid + InvP.PaidAmount.Value;
                            }
                            if (InvP.ItemCode == "REENF")
                            {
                                if (InvP.PaidAmount.HasValue)

                                    Payment.ReenrollAmountPaid = Payment.ReenrollAmountPaid + InvP.PaidAmount.Value;
                            }
                        }

                    }
                    return Payment;
                }
                else
                {
                    return null;
                }

            }
        }
        public string GetStudentAttention(string studentID)
        {
            var query = from cc in DS.cicmpies
                        where cc.textfield4 == studentID
                        select cc;
            if (query.Count() > 0)
            {
                return query.First().TextField13;
            }
            else
            {
                return "";
            }
        }
        public ISHCMC_ListInvoiceDetail GetInvoiceDetail(ISHCMC_Invoice Inv)
        {

            ISHCMC_V_StudentListWithDebtor student = DS.ISHCMC_V_StudentListWithDebtors.Single(c => c.StudentID == Inv.StudentID);



            ISHCMC_ListInvoiceDetail InvDetail = new ISHCMC_ListInvoiceDetail();

            if (student.InvoiceDebtor != null)
            {
                cicmpy invoicedebtor = DS.cicmpies.Single(c => c.debnr == student.InvoiceDebtor);
                if (student.BillingAddress1.Length == 0
            && student.BillingAddress2.Length == 0
            && student.BillingAddress3.Length == 0)
                {
                    InvDetail.BillingName = invoicedebtor.cmp_name.ToUpper();
                    InvDetail.BillingAddr1 = invoicedebtor.cmp_fadd1;
                    InvDetail.BillingAddr2 = invoicedebtor.cmp_fadd2;
                    InvDetail.BillingAddr3 = invoicedebtor.cmp_fadd3;
                }
                else
                {
                    InvDetail.BillingName = invoicedebtor.cmp_name.ToUpper();
                    InvDetail.BillingAddr1 = student.BillingAddress1;
                    InvDetail.BillingAddr2 = student.BillingAddress2;
                    InvDetail.BillingAddr3 = student.BillingAddress3;
                }
            }


            var querySec = from Section in DS.ISHCMC_Sections
                         //  where Section.SectionCode == Inv.Section && Section.DueDate == Inv.DueDate
                           where Section.SectionCode == Inv.Section && Section.School == student.School
                           select Section;
            ISHCMC_InvoiceHeader InvHeader = GetInvoiceHeader(student.StudentID, Inv.SchoolYear,student.School);
            ISHCMC_Section itemSec = querySec.First();
            InvDetail.CurrentGrade = student.Grade;
            InvDetail.DueDate = Inv.DueDate;
            InvDetail.StudentID = Inv.StudentID;
            InvDetail.StudentName = student.StudentName;

            InvDetail.yourref = Inv.ProformaInvoiceNo;
            InvDetail.Invoicedate = Inv.TransDate;
            IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);
            if (student.EnrollmentDate.HasValue)
            {
                InvDetail.EnrolmentDate =  student.EnrollmentDate.Value;
            }//Convert.ToDateTime("01/08/" + DateTime.Now.Year.ToString(), culture);
            InvDetail.description = itemSec.Descriptions;
            InvDetail.SectionNo = Inv.Section;
            InvDetail.itemcode = Inv.Itemcode;
            InvDetail.Discount = Convert.ToDouble(Inv.Discount);
            InvDetail.Price = Convert.ToDouble(Inv.Amount);
            InvDetail.NetPrice = InvDetail.Price - InvDetail.Price * (InvDetail.Discount / 100);
            return InvDetail;
        }
        public ISHCMC_ListInvoiceDetail_DebitNote GetInvoiceDetail(ISHCMC_Invoice Inv, string itemcode)
        {

            ISHCMC_V_StudentListWithDebtor student = DS.ISHCMC_V_StudentListWithDebtors.Single(c => c.StudentID == Inv.StudentID);

            ISHCMC_ListInvoiceDetail_DebitNote InvDetail = new ISHCMC_ListInvoiceDetail_DebitNote();
            if (student.InvoiceDebtor != null)
            {
                cicmpy invoicedebtor = DS.cicmpies.Single(c => c.debnr == student.InvoiceDebtor);
                if (student.BillingAddress1.Length == 0
            && student.BillingAddress2.Length == 0
            && student.BillingAddress3.Length == 0)
                {
                    InvDetail.BillingName = invoicedebtor.cmp_name.ToUpper();
                    InvDetail.BillingAddr1 = invoicedebtor.cmp_fadd1;
                    InvDetail.BillingAddr2 = invoicedebtor.cmp_fadd2;
                    InvDetail.BillingAddr3 = invoicedebtor.cmp_fadd3;
                }
                else
                {
                    InvDetail.BillingName = invoicedebtor.cmp_name.ToUpper();
                    InvDetail.BillingAddr1 = student.BillingAddress1;
                    InvDetail.BillingAddr2 = student.BillingAddress2;
                    InvDetail.BillingAddr3 = student.BillingAddress3;
                }
            }

            ISHCMC_InvoiceHeader InvHeader = GetInvoiceHeader(student.StudentID, Inv.SchoolYear,student.School);
            InvDetail.CurrentGrade = student.Grade;
            InvDetail.DueDate = Inv.DueDate;
            InvDetail.StudentID = Inv.StudentID;
            InvDetail.StudentName = student.StudentName;

            InvDetail.yourref = Inv.InvoiceNo;
            InvDetail.Invoicedate = Inv.TransDate;
            IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);
            if (student.EnrollmentDate.HasValue)
            {
                InvDetail.EnrolmentDate = student.EnrollmentDate.Value;
            }//Convert.ToDateTime("01/08/" + DateTime.Now.Year.ToString(), culture);
            InvDetail.description = Inv.Section;
            InvDetail.itemcode = Inv.Itemcode;
            InvDetail.Discount = Convert.ToDouble(Inv.Discount);
            InvDetail.Price = Convert.ToDouble(Inv.Amount);
            InvDetail.NetPrice = InvDetail.Price - InvDetail.Price * (InvDetail.Discount / 100);
            return InvDetail;
        }
        public int DeleteDiscount(int disID)
        {
            try
            {
                ISHCMC_Discount dis = DS.ISHCMC_Discounts.Single(c => c.ID == disID);
                dis.Status = 1;
                DS.SubmitChanges();
                return 1;
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        public List<string> GetListProformaNo(ISHCMC_V_StudentListWithDebtor student,string schoolyear,string sectioncode)
        {
            var query = from Inv in DS.ISHCMC_Invoices
                        where Inv.StudentID == student.StudentID
                        && Inv.Status == 0 && Inv.SchoolYear == schoolyear && Inv.Section == sectioncode
                        select Inv;
            Hashtable hashInv = new Hashtable();
            foreach (ISHCMC_Invoice itemInv in query)
            {
                hashInv[itemInv.ProformaInvoiceNo] = 0;
            }

            List<string> ListPro = new List<string>();
            foreach (object key in hashInv.Keys)
            {
                ListPro.Add(key.ToString());
            }
            return ListPro;
        }
        public bool CheckInvoicedValidated(string refer)
        {
            var query = from InvExact in DS.frsrgs
                        where InvExact.docnumber == refer
                        select InvExact;
            if (query.Count() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public string GetJournalNumber(string glaccount)
        {
            var query = from Jnr in DS.dagbks
                        where Jnr.reknr == glaccount
                        select Jnr;
            if (query.Count() > 0)
            {
                return query.First().dagbknr.ToString();
            }
            else
            {
                return "";
            }
        }

        public bool CheckDebtorRegisterExist(string studentID)
        {
            var query = from DT in DS.ISHCMC_DebtorRegisters
                        where DT.StudentIDIS == studentID
                        select DT;
            if (query.Count() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public string GetClassification(string debnr)
        {
            string payertype = "";
            var query = from cc in DS.cicmpies
                        where cc.debnr == debnr
                        select cc;
            if (query.Count() > 0)
            {
                payertype = query.First().ClassificationId;
                return payertype;
            }
            else
            {
                return "";
            }

        }

        #region Amount in word
        private string Chu(string gNumber)
        {
            string result = "";
            switch (gNumber)
            {
                case "0":
                    result = "không";
                    break;
                case "1":
                    result = "một";
                    break;
                case "2":
                    result = "hai";
                    break;
                case "3":
                    result = "ba";
                    break;
                case "4":
                    result = "bốn";
                    break;
                case "5":
                    result = "năm";
                    break;
                case "6":
                    result = "sáu";
                    break;
                case "7":
                    result = "bảy";
                    break;
                case "8":
                    result = "tám";
                    break;
                case "9":
                    result = "chín";
                    break;
            }
            return result;
        }
        private string Donvi(string so)
        {
            string Kdonvi = "";

            if (so.Equals("1"))
                Kdonvi = "";
            if (so.Equals("2"))
                Kdonvi = "nghìn";
            if (so.Equals("3"))
                Kdonvi = "triệu";
            if (so.Equals("4"))
                Kdonvi = "tỷ";
            if (so.Equals("5"))
                Kdonvi = "nghìn tỷ";
            if (so.Equals("6"))
                Kdonvi = "triệu tỷ";
            if (so.Equals("7"))
                Kdonvi = "tỷ tỷ";

            return Kdonvi;
        }
        private string Tach(string tach3)
        {
            string Ktach = "";
            if (tach3.Equals("000"))
                return "";
            if (tach3.Length == 3)
            {
                string tr = tach3.Trim().Substring(0, 1).ToString().Trim();
                string ch = tach3.Trim().Substring(1, 1).ToString().Trim();
                string dv = tach3.Trim().Substring(2, 1).ToString().Trim();
                if (tr.Equals("0") && ch.Equals("0"))
                    Ktach = " không trăm lẻ " + Chu(dv.ToString().Trim()) + " ";
                if (!tr.Equals("0") && ch.Equals("0") && dv.Equals("0"))
                    Ktach = Chu(tr.ToString().Trim()).Trim() + " trăm ";
                if (!tr.Equals("0") && ch.Equals("0") && !dv.Equals("0"))
                    Ktach = Chu(tr.ToString().Trim()).Trim() + " trăm lẻ " + Chu(dv.Trim()).Trim() + " ";
                if (tr.Equals("0") && Convert.ToInt32(ch) > 1 && Convert.ToInt32(dv) > 0 && !dv.Equals("5"))
                    Ktach = " không trăm " + Chu(ch.Trim()).Trim() + " mươi " + Chu(dv.Trim()).Trim() + " ";
                if (tr.Equals("0") && Convert.ToInt32(ch) > 1 && dv.Equals("0"))
                    Ktach = " không trăm " + Chu(ch.Trim()).Trim() + " mươi ";
                if (tr.Equals("0") && Convert.ToInt32(ch) > 1 && dv.Equals("5"))
                    Ktach = " không trăm " + Chu(ch.Trim()).Trim() + " mươi lăm ";
                if (tr.Equals("0") && ch.Equals("1") && Convert.ToInt32(dv) > 0 && !dv.Equals("5"))
                    Ktach = " không trăm mười " + Chu(dv.Trim()).Trim() + " ";
                if (tr.Equals("0") && ch.Equals("1") && dv.Equals("0"))
                    Ktach = " không trăm mười ";
                if (tr.Equals("0") && ch.Equals("1") && dv.Equals("5"))
                    Ktach = " không trăm mười lăm ";
                if (Convert.ToInt32(tr) > 0 && Convert.ToInt32(ch) > 1 && Convert.ToInt32(dv) > 0 && !dv.Equals("5"))
                    Ktach = Chu(tr.Trim()).Trim() + " trăm " + Chu(ch.Trim()).Trim() + " mươi " + Chu(dv.Trim()).Trim() + " ";
                if (Convert.ToInt32(tr) > 0 && Convert.ToInt32(ch) > 1 && dv.Equals("0"))
                    Ktach = Chu(tr.Trim()).Trim() + " trăm " + Chu(ch.Trim()).Trim() + " mươi ";
                if (Convert.ToInt32(tr) > 0 && Convert.ToInt32(ch) > 1 && dv.Equals("5"))
                    Ktach = Chu(tr.Trim()).Trim() + " trăm " + Chu(ch.Trim()).Trim() + " mươi lăm ";
                if (Convert.ToInt32(tr) > 0 && ch.Equals("1") && Convert.ToInt32(dv) > 0 && !dv.Equals("5"))
                    Ktach = Chu(tr.Trim()).Trim() + " trăm mười " + Chu(dv.Trim()).Trim() + " ";

                if (Convert.ToInt32(tr) > 0 && ch.Equals("1") && dv.Equals("0"))
                    Ktach = Chu(tr.Trim()).Trim() + " trăm mười ";
                if (Convert.ToInt32(tr) > 0 && ch.Equals("1") && dv.Equals("5"))
                    Ktach = Chu(tr.Trim()).Trim() + " trăm mười lăm ";

            }


            return Ktach;

        }
        public string So_chu(double gNum, string currency)
        {
            if (gNum == 0)
                return "0 " + currency;

            string lso_chu = "";
            string tach_mod = "";
            string tach_conlai = "";
            double Num = Math.Round(gNum, 0);
            string gN = Convert.ToString(Num);
            int m = Convert.ToInt32(gN.Length / 3);
            int mod = gN.Length - m * 3;
            string dau = "[+]";

            // Dau [+ , - ]
            if (gNum < 0)
                dau = "[-]";
            dau = "";

            // Tach hang lon nhat
            if (mod.Equals(1))
                tach_mod = "00" + Convert.ToString(Num.ToString().Trim().Substring(0, 1)).Trim();
            if (mod.Equals(2))
                tach_mod = "0" + Convert.ToString(Num.ToString().Trim().Substring(0, 2)).Trim();
            if (mod.Equals(0))
                tach_mod = "000";
            // Tach hang con lai sau mod :
            if (Num.ToString().Length > 2)
                tach_conlai = Convert.ToString(Num.ToString().Trim().Substring(mod, Num.ToString().Length - mod)).Trim();

            ///don vi hang mod 
            int im = m + 1;
            if (mod > 0)
                lso_chu = Tach(tach_mod).ToString().Trim() + " " + Donvi(im.ToString().Trim());
            /// Tach 3 trong tach_conlai

            int i = m;
            int _m = m;
            int j = 1;
            string tach3 = "";
            string tach3_ = "";

            while (i > 0)
            {
                tach3 = tach_conlai.Trim().Substring(0, 3).Trim();
                tach3_ = tach3;
                lso_chu = lso_chu.Trim() + " " + Tach(tach3.Trim()).Trim();
                m = _m + 1 - j;
                if (!tach3_.Equals("000"))
                    lso_chu = lso_chu.Trim() + " " + Donvi(m.ToString().Trim()).Trim();
                tach_conlai = tach_conlai.Trim().Substring(3, tach_conlai.Trim().Length - 3);

                i = i - 1;
                j = j + 1;
            }
            if (lso_chu.Trim().Substring(0, 1).Equals("k"))
                lso_chu = lso_chu.Trim().Substring(10, lso_chu.Trim().Length - 10).Trim();
            if (lso_chu.Trim().Substring(0, 1).Equals("l"))
                lso_chu = lso_chu.Trim().Substring(2, lso_chu.Trim().Length - 2).Trim();
            if (lso_chu.Trim().Length > 0)
                lso_chu = dau.Trim() + " " + lso_chu.Trim().Substring(0, 1).Trim().ToUpper() + lso_chu.Trim().Substring(1, lso_chu.Trim().Length - 1).Trim();

            return lso_chu.ToString().Trim() + "  " + currency;

        }
        #endregion


        #region Process Refund School Fee
        public void UpdateInvoiceRefund(ISHCMC_V_StudentListWithDebtor itemStudent)
        {
            string[] tokenSHY = itemStudent.SchoolYear.Split(new char[1] { '-' });

            var queryInv = from Inv in DS.ISHCMC_Invoices
                           where Inv.StudentID == itemStudent.StudentID
                           && Inv.SchoolYear == tokenSHY[1]
                           select Inv;
            foreach (ISHCMC_Invoice itemInvoice in queryInv)
            {
                if (itemInvoice.Itemcode == "TF")
                {
                    ISHCMC_Invoice UpdateInv = DS.ISHCMC_Invoices.Single(c => c.ID == itemInvoice.ID);
                    UpdateInv.Status = 2;
                }
                if (itemInvoice.Itemcode == "ADF")
                {
                    ISHCMC_Invoice UpdateInv = DS.ISHCMC_Invoices.Single(c => c.ID == itemInvoice.ID);
                    UpdateInv.Status = 2;
                }

            }
            DS.SubmitChanges();

        }
        private string GetTermInCalendar(DateTime WithdrawDate, string SchoolYear)
        {
            string strTerm = "";
            var queryTerm = from Term in DS.ISHCMC_SchoolSchedules
                            where Term.SchoolYear == SchoolYear
                            && Term.StartDate <= WithdrawDate && Term.EndDate >= WithdrawDate
                            select Term;
            if (queryTerm.Count() >0)
            {
               strTerm = queryTerm.First().Term;
            }
            return strTerm;
        }
        private string GetGroupRefundPolicy(int NoOfDay)
        {
            string strGroup = "";

            var queryGroup = from Group in DS.ISHCMC_SchoolGroupPolicyRefunds
                             where Group.MinDay <= NoOfDay && NoOfDay <= Group.MaxDay
                             select Group;
            if (queryGroup.Count() > 0)
            {
                strGroup = queryGroup.First().GroupCode;
            }

            return strGroup;
        }
        private decimal GetRefundPercent(string itemCode, string termCode, string groupPolicy)
        {
            decimal Percent = 0;
            var queryRefund = from Refund in DS.ISHCMC_SchoolPolicyRefunds
                              where Refund.GroupPolicy == groupPolicy
                              && Refund.ItemCode == itemCode
                              && Refund.TermCode == termCode
                              select Refund;
            if (queryRefund.Count() > 0)
            {
                Percent = queryRefund.First().Percentage.Value;
            }
            return Percent;
        }
        public void GenerateInvoiceRefund(string itemcode, ISHCMC_V_StudentListWithDebtor itemStudent, string schoolyear, DateTime InvoiceDate) // type : Education or NonEducation
        {



            string invoiceNo = "";
            string profromInv = "";
            string strProfromInv = "";
            ISHCMC_InvoiceHeader InvHeader = new ISHCMC_InvoiceHeader();

            DateTime duedatePayment;

            decimal amountPercentRefund = 0;
            decimal amount = 0;
            string descriptioninvoice = "";
            if (InvoiceDate.Date < Convert.ToDateTime("01/06/" + itemStudent.SchoolYear.Split(new char[1] { '-' })[0], culture))
            {
                duedatePayment = Convert.ToDateTime("01/07/" + itemStudent.SchoolYear.Split(new char[1] { '-' })[0], culture);
            }

            if (CheckInvoiceHeaderExist(itemStudent.StudentID, schoolyear, itemStudent.School))
            {
                InvHeader = this.GetInvoiceHeader(itemStudent.StudentID, schoolyear, itemStudent.School);

                duedatePayment = InvoiceDate.Date.AddDays(7);
            }

            else
            {

                duedatePayment = InvoiceDate.Date.AddDays(30);
                ISHCMC_InvoiceHeader InvHeaderAdd = new ISHCMC_InvoiceHeader()
                {
                    Debcode = itemStudent.DebtorCode,
                    Debnr = itemStudent.DebtorCode.Trim(),
                    StudentID = itemStudent.StudentID,
                    Status = 0,

                    DueDate = duedatePayment,
                    SchoolYear = schoolyear,
                    School = itemStudent.School,
                    InvoiceDisplayID = this.GetInvoiceHeaderDisplayMaxID(itemStudent.School) + 1,
                    Syscreated = DateTime.Now,
                    Sysmodified = DateTime.Now,
                    Syscreator = Environment.UserName,
                    Sysmodifier = Environment.UserName

                };
                DS.ISHCMC_InvoiceHeaders.InsertOnSubmit(InvHeaderAdd);
                DS.SubmitChanges();
                InvHeader = this.GetInvoiceHeader(itemStudent.StudentID, schoolyear, itemStudent.School);

            }
            string[] tokenSHY = itemStudent.SchoolYear.Split(new char[1] { '-' });
            var querySec = from Section in DS.ISHCMC_Sections
                           where Section.SectionCode == "A" && Section.School == itemStudent.School
                           && Section.SchoolYear == tokenSHY[1]
                           select Section;
            ISHCMC_Section itemSec = querySec.First();
            int invoiceDisplayID = InvHeader.InvoiceDisplayID.Value;
            if (!CheckInvoiceExist(itemStudent, itemcode, schoolyear, InvoiceDate, itemSec))
            {






                ISHCMC_V_Item itemFee = DS.ISHCMC_V_Items.Single(c => c.ItemCode == itemcode);

                string pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "_"
                    + "A" + "_" + schoolyear.Substring(2, 2);
                if (itemStudent.Grade.StartsWith("E2"))
                {
                    if (itemStudent.HalfDay == 0)
                    {
                        pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "F_"
                        + "A" + "_" + schoolyear.Substring(2, 2);
                    }
                    else if (itemStudent.HalfDay == 1)
                    {
                        pricelistcode = itemStudent.School.Substring(0, 2) + "_" + itemStudent.Grade + "H_"
                     + "A" + "_" + schoolyear.Substring(2, 2);
                    }
                }
                staffl itemPL = new staffl();
                stfom itemFM = new stfom();

                if (itemFee.Assortment == 1 && itemFee.ItemCode == "TF")
                {
                    itemFM = DS.stfoms.First(c => c.prijslijst == pricelistcode);
                    itemPL = DS.staffls.First(c => c.prijslijst == pricelistcode);
                }
                else
                {
                    itemPL = DS.staffls.First(c => c.prijslijst == itemStudent.School && c.artcode == itemcode);
                    itemFM = DS.stfoms.First(c => c.prijslijst == itemPL.prijslijst);
                }




                invoiceNo = invoiceDisplayID.ToString();
                profromInv = invoiceDisplayID.ToString();
                while (invoiceNo.Length < 5)
                {
                    invoiceNo = "0" + invoiceNo;
                    profromInv = "0" + profromInv;
                }



                strProfromInv = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + profromInv + "-" + "A" + "-" + itemStudent.School;
                amount = Convert.ToDecimal(itemPL.prijs83);

                /* Begin Process Refund
                 
                 */
                string termCode = this.GetTermInCalendar(itemStudent.ActualWithdrawDate.Value, itemStudent.SchoolYear);

                string groupPolicy = this.GetGroupRefundPolicy(itemStudent.NoOfDay.Value);

                amountPercentRefund = this.GetRefundPercent(itemcode, termCode, groupPolicy);

                amount = ((100 - amountPercentRefund) / 100) * amount;


                invoiceNo = (Convert.ToInt32(schoolyear) - 1).ToString().Substring(2, 2) + "-" + invoiceNo + "-" + "A" + "-TF-" + itemStudent.School.Substring(0, 2);
                if (itemFee.ItemCode == "ADF")
                {
                    descriptioninvoice = "Annual Development Fee " + itemStudent.SchoolYear;
                }
                if (itemFee.ItemCode == "TF")
                {
                    descriptioninvoice = "School Fee " + itemStudent.SchoolYear;
                }


                ISHCMC_Invoice itemInv = new ISHCMC_Invoice
              {
                  Debcode = itemStudent.DebtorCode,
                  Debnr = itemStudent.DebtorCode.Trim(),
                  StudentID = itemStudent.StudentID,

                  Itemcode = itemcode,
                  Amount = amount,
                  Discount = this.CalculateDiscount(itemStudent.DebtorCode, itemStudent.StudentID, itemFee.ItemCode),

                  TransDate = InvoiceDate.Date,
                  Status = 0,
                  Section = "A",
                  Description = descriptioninvoice,
                  DueDate = duedatePayment,
                  SchoolYear = schoolyear,
                  SpecialDiscount = false,
                  Authorize = false,
                  InvoiceHeaderID = InvHeader.ID,
                  InvoiceNo = invoiceNo,
                  ProformaInvoiceNo = strProfromInv,
                  Currency = itemFM.valcode,
                  Syscreated = DateTime.Now,
                  Sysmodified = DateTime.Now,
                  Syscreator = Environment.UserName,
                  Sysmodifier = Environment.UserName



              };
             
                DS.ISHCMC_Invoices.InsertOnSubmit(itemInv);
                DS.SubmitChanges();
                this.ProcessTrackingInvoice(itemInv, "Create new invoice detail", "Create new");

            }



        }





        
        #endregion

        #region Process Fixed Term
        public void UpdateInvoiceFixTerm(ISHCMC_V_StudentListWithDebtor itemStudent)
        {
            string[] tokenSHY = itemStudent.SchoolYear.Split(new char[1] { '-' });

            var queryInv = from Inv in DS.ISHCMC_Invoices
                           where Inv.StudentID == itemStudent.StudentID
                           && Inv.SchoolYear == tokenSHY[1]
                           select Inv;
            foreach (ISHCMC_Invoice itemInvoice in queryInv)
            {
                if (itemInvoice.Itemcode == "TF")
                {
                    ISHCMC_Invoice UpdateInv = DS.ISHCMC_Invoices.Single(c => c.ID == itemInvoice.ID);
                    UpdateInv.Status = 3;
                }
                if (itemInvoice.Itemcode == "ADF")
                {
                    ISHCMC_Invoice UpdateInv = DS.ISHCMC_Invoices.Single(c => c.ID == itemInvoice.ID);
                    UpdateInv.Status = 3;
                }

            }
            DS.SubmitChanges();

        }
        #endregion

    }
}
